﻿/// -----------------------------------------------------------------
/// RelationLoader.cs: Helper class to create Objects from Segment Relation entries in the Database.
/// License: see http://mve.codeplex.com/license; All Rights follows the MS-PL
/// Current owner: shiniwa
/// The project decription: please refer to http://codeplex.com/mve/
/// -----------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace MVEContentSvc.RelationsOperations
{
    /*
     * 日本SGI株式会社
     * ヨハンソン・ヘンリク
     * johansson@sgi.co.jp
     * Project: MVEPublicService
     * 
     * Comments:
     *      No Comments.
     * 
     * Change Log:
     * ---------------------------------------------------------------------------
     * 1/8/2009 12:03:33 PM		Johansson Henrik		Class Created.
     * 
     */

    /// <summary>
    /// Helper class to create Objects from Segment Relation entries in the Database.
    /// Use this class to Load Segments Relations from the Databse.
    /// </summary>
    /// <example>Relation rel = LoadRelationFromDatabase(1, sqlConnection);</example>
    /// <seealso cref="ClipOperations.Segment"/>
    /// <remarks>INSERT:Creds</remarks>
    internal static class RelationLoader
    {
        /// <summary>
        /// Loads a single Relation from the DB into a Relation object.
        /// Requires that no other commands are open on the connection used to fetch the Relation.
        /// </summary>
        /// <param name="relationId">Id of Relation to load</param>
        /// <param name="connection">Sql Connection</param>
        /// <returns>Object Representation of the loaded Relation.</returns>
        internal static Relation LoadRelationFromDatabase(int relationId, SqlConnection connection)
        {
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;
            command.CommandTimeout = 60;

            Relation result = null;

            try
            {
                command.Connection = connection;
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "GetRelationById";

                command.Parameters.Add("relationId", SqlDbType.Int).Value = relationId;
                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();
                    result = new Relation()
                    {
                        Id = reader.GetInt32(3),
                        relation = reader.GetByte(2),
                        segment2Id = reader.GetInt32(1),
                        segment1Id = reader.GetInt32(0)
                    };
                }
            }
            catch (NullReferenceException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = null;
            }
            catch (SqlException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = null;
            }
            catch (InvalidOperationException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = null;
            }
            catch (IndexOutOfRangeException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = null;
            }
            finally
            {
                command.Dispose();
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
            }

            return result;
        }//end:LoadRelationFromDatabase

        /// <summary>
        /// Loads all related segment Ids containing the given segmentId 
        /// from the DB into an id (int) List object.
        /// Requires that no other commands are open on the connection used to fetch the Relation.
        /// </summary>
        /// <param name="segmentId">Id of Segment whose related segment ids to load</param>
        /// <param name="connection">Sql Connection</param>
        /// <returns>List Object Representation of the discovered related Segment Ids.</returns>
        internal static List<int> FindRelatedSegmentIdsInDatabase(int segmentId, SqlConnection connection)
        {
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;
            command.CommandTimeout = 60;

            List<int> result = new List<int>();

            List<Relation> _discoveredRelations =
                new List<Relation>();

            try
            {
                command.Connection = connection;
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "GetRelatedSegmentsBySegmentId";

                command.Parameters.Add("segmentId", SqlDbType.Int).Value = segmentId;
                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        _discoveredRelations.Add(new Relation()
                        {
                            Id = reader.GetInt32(3),
                            relation = reader.GetByte(2),
                            segment2Id = reader.GetInt32(1),
                            segment1Id = reader.GetInt32(0)
                        });
                    }
                }

                //get the unique related ideas...
                foreach (Relation rel in _discoveredRelations)
                {
                    if (rel.segment1Id != segmentId)
                    {
                        if (result.Contains(rel.segment1Id) == false)
                            result.Add(rel.segment1Id);
                    }

                    if (rel.segment2Id != segmentId)
                    {
                        if (result.Contains(rel.segment2Id) == false)
                            result.Add(rel.segment2Id);
                    }
                }

            }
            catch (NullReferenceException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = new List<int>();
            }
            catch (SqlException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = new List<int>();
            }
            catch (InvalidOperationException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = new List<int>();
            }
            catch (IndexOutOfRangeException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = new List<int>();
            }
            finally
            {
                command.Dispose();
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
            }

            return result;
        }//end:FindRelatedSegmentIdsInDatabase
    }
}
